package com.yxjx.duoxue.database;

import java.util.ArrayList;

import com.yxjx.duoxue.Yx;
import com.yxjx.duoxue.datamodel.DMCallRecord;
import com.yxjx.duoxue.datamodel.DMOpenApp;
import com.yxjx.duoxue.message.ClientMessageListDTS;
import com.yxjx.duoxue.utils.UIHelper;
import com.yxjx.duoxue.utils.Utils;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class YxDataBase {
	
	public static final String TABLE_NAME_SEARCH_HISTORY = "search_history";
	public static final String TABLE_NAME_CALL_RECORD = "call_record";
	public static final String TABLE_NAME_OPEN_APP_RECORD = "open_app_record";
	public static final String TABLE_VISITED_MESSAGES = "visited_messages";
	public static final String KEY_ID = "id";
	public static final String KEY_WORD = "word";
	
	public static final String KEY_SID = "sid";
	public static final String KEY_TYPE = "type";
	public static final String KEY_VERSION = "version";
	public static final String KEY_DEVICE = "device";
	public static final String KEY_DB_ID = "_id";
	public static final String KEY_MARK = "mark";
	public static final String KEY_MESSAGE_ID = "message_id";
	// 消息是否被查看过：0为未读过，1在列表查看过，2点击过
	public static final String KEY_MESSAGE_VISITED = "has_visted";
	
	private SQLiteDatabase mDatabase;
	private SQLiteOpenHelper mSQLiteOpenHelper;
	private Context mContext = null;
	
	private static YxDataBase sInstance;
	
	public static YxDataBase getInstance(Context context) {
		if (sInstance == null) {
			sInstance = new YxDataBase(context);
		}
		return sInstance;
	}
	
	private YxDataBase(Context context) {
		mContext = context;
		mSQLiteOpenHelper = new SQLiteOpenHelper(context, Yx.DB_NAME, null, Yx.DB_VERSION) {
			
			@Override
			public void onUpgrade(SQLiteDatabase database, int oldVersion, int newVersion) {
				if (newVersion >= 2) {
					// 新增searchHistory表和电话记录
					database.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_SEARCH_HISTORY);
					database.execSQL("CREATE TABLE " + TABLE_NAME_SEARCH_HISTORY
							+ " (_id INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_ID
							+ " INTEGER," + KEY_WORD + " TEXT)");
					
					database.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_CALL_RECORD);
					database.execSQL("CREATE TABLE " + TABLE_NAME_CALL_RECORD
							+ " (_id INTEGER PRIMARY KEY AUTOINCREMENT, "
							+ KEY_SID + " TEXT, " + KEY_VERSION + " TEXT, "
							+ KEY_DEVICE + " TEXT, " + KEY_ID + " INTEGER, "
							+ KEY_TYPE + " INTEGER)");
				}
				
				if (newVersion >= 3) {
					database.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_OPEN_APP_RECORD);
					database.execSQL("CREATE TABLE " + TABLE_NAME_OPEN_APP_RECORD
							+ " (_id INTEGER PRIMARY KEY AUTOINCREMENT, "
							+ KEY_VERSION + " TEXT, "
							+ KEY_DEVICE + " TEXT, "
							+ KEY_MARK + " TEXT)");
				}
				if (newVersion >= 4) {
					addMessageVisitedTable(database);
				}
			}
			
			@Override
			public void onCreate(SQLiteDatabase database) {
				// 新增searchHistory表和电话记录
				database.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_SEARCH_HISTORY);
				database.execSQL("CREATE TABLE " + TABLE_NAME_SEARCH_HISTORY
						+ " (_id INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_ID
						+ " INTEGER," + KEY_WORD + " TEXT)");
				
				database.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_CALL_RECORD);
				database.execSQL("CREATE TABLE " + TABLE_NAME_CALL_RECORD
						+ " (_id INTEGER PRIMARY KEY AUTOINCREMENT, "
						+ KEY_SID + " TEXT, " + KEY_VERSION + " TEXT, "
						+ KEY_DEVICE + " TEXT, " + KEY_ID + " INTEGER, "
						+ KEY_TYPE + " INTEGER)");
				// 打开APP的记录
				database.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_OPEN_APP_RECORD);
				database.execSQL("CREATE TABLE " + TABLE_NAME_OPEN_APP_RECORD
						+ " (_id INTEGER PRIMARY KEY AUTOINCREMENT, "
						+ KEY_VERSION + " TEXT, "
						+ KEY_DEVICE + " TEXT, "
						+ KEY_MARK + " TEXT)");
				// 消息是否已经读过缓存
				addMessageVisitedTable(database);
			}
		};
		
	}
	
	// 缓存消息中心的信息
	
	private void addMessageVisitedTable(SQLiteDatabase database) {
		database.execSQL("DROP TABLE IF EXISTS " + TABLE_VISITED_MESSAGES);
		database.execSQL("CREATE TABLE " + TABLE_VISITED_MESSAGES
				+ " (_id INTEGER PRIMARY KEY AUTOINCREMENT, "
				+ KEY_MESSAGE_ID + " TEXT, "
				+ KEY_MESSAGE_VISITED + " INTEGER)");
	}
	
	public void markMessageAsChecked(ClientMessageListDTS item) {
		// 单个message置为2
		if (item == null) {
			return;
		}
		mDatabase = mSQLiteOpenHelper.getWritableDatabase();
		try {
			mDatabase.beginTransaction();

			ContentValues cv = new ContentValues();
			cv.put(KEY_MESSAGE_VISITED, 2);
			mDatabase.update(TABLE_VISITED_MESSAGES, cv, KEY_MESSAGE_ID + " =? ",
					new String[] { "" + item.getId() });
			mDatabase.setTransactionSuccessful();
		} catch (Exception e) {
			String str = " " + e;
		} finally {
			mDatabase.endTransaction();
		}
		mDatabase.close();
	}
	
	public void markAllMessageListVisited() {
		// 所有message置为1
		mDatabase = mSQLiteOpenHelper.getWritableDatabase();
		try {
			mDatabase.beginTransaction();

			ContentValues cv = new ContentValues();
			cv.put(KEY_MESSAGE_VISITED, 1);
			mDatabase.update(TABLE_VISITED_MESSAGES, cv, "",
					null);
			mDatabase.setTransactionSuccessful();
		} catch (Exception e) {
			String str = " " + e;
		} finally {
			mDatabase.endTransaction();
		}
		mDatabase.close();
		
	}
	
	public boolean insertMessagesToDb(ArrayList<ClientMessageListDTS> messages) {
		if (messages == null || messages.size() == 0) {
			return false;
		}
		mDatabase = mSQLiteOpenHelper.getWritableDatabase();
		try {
			mDatabase.beginTransaction();
			for (ClientMessageListDTS message : messages) {
				boolean isExist = false;
				Cursor c = mDatabase.rawQuery("SELECT * FROM " + TABLE_VISITED_MESSAGES + " WHERE " + KEY_MESSAGE_ID + " = " + message.getId(), null);
				if (c.getCount() > 0) {
					isExist = true;
					while(c.moveToNext()) {
						message.setVisited(c.getInt(c.getColumnIndex(KEY_MESSAGE_VISITED)) == 2);
					}
				}
				c.close();
				
				if (!isExist) {
					ContentValues cv = new ContentValues();
					cv.put(KEY_MESSAGE_ID, "" + message.getId());
					cv.put(KEY_MESSAGE_VISITED, 1);
					mDatabase.insertOrThrow(TABLE_VISITED_MESSAGES, null, cv);
				}
			}
			mDatabase.setTransactionSuccessful();

		} catch (SQLiteException e) {
		} finally {
			mDatabase.endTransaction();
		}
		mDatabase.close();
		return true;
	}
	
	public boolean queryHasMessageNoVisited(ArrayList<ClientMessageListDTS> newMessages) {
		// 新query的第一条message不存在db
		if (newMessages == null || newMessages.size() <= 0) {
			return false;
		}
		ClientMessageListDTS firstMessage = newMessages.get(0);
		
		ArrayList<ClientMessageListDTS> ret = new ArrayList<ClientMessageListDTS>();
		mDatabase = mSQLiteOpenHelper.getWritableDatabase();
		try {
			mDatabase.beginTransaction();
			Cursor c = mDatabase.rawQuery("SELECT * FROM " + TABLE_VISITED_MESSAGES + " WHERE " + KEY_MESSAGE_ID + " = " + firstMessage.getId(), null);
			while (c.moveToNext()) {
				ClientMessageListDTS record = new ClientMessageListDTS();
				ret.add(record);
			}
			c.close();
			mDatabase.setTransactionSuccessful();

		} catch (SQLiteException e) {
		} finally {
			mDatabase.endTransaction();
		}
		mDatabase.close();
		
		return ret.size() == 0;
	}
	
	public boolean queryIfAMessageChecked(ClientMessageListDTS item) {
		// 当前message是否未2
		if (item == null) {
			return false;
		}
		
		ArrayList<ClientMessageListDTS> ret = new ArrayList<ClientMessageListDTS>();
		mDatabase = mSQLiteOpenHelper.getWritableDatabase();
		try {
			mDatabase.beginTransaction();
			Cursor c = mDatabase.rawQuery("SELECT * FROM " + TABLE_VISITED_MESSAGES + " WHERE " + KEY_MESSAGE_ID + " = " + item.getId()
					+ " and " + KEY_MESSAGE_VISITED + " = " +  "2", null);
			while (c.moveToNext()) {
				ClientMessageListDTS record = new ClientMessageListDTS();
				record.setId(c.getInt(c.getColumnIndex(KEY_MESSAGE_ID)));
				ret.add(record);
			}
			c.close();
			mDatabase.setTransactionSuccessful();

		} catch (SQLiteException e) {
		} finally {
			mDatabase.endTransaction();
		}
		mDatabase.close();
		return ret.size() != 0;
	}
	// 缓存消息中心的信息
	
	public void insertSearchHistory(String word) {
		mDatabase = mSQLiteOpenHelper.getWritableDatabase();
		try {
			mDatabase.beginTransaction();

			// 删除存在的word记录
			mDatabase.delete(TABLE_NAME_SEARCH_HISTORY, KEY_WORD + "=?", new String[]{word});
			
			// 插入word记录
			ContentValues cv = new ContentValues();
			cv.put(KEY_WORD, word);
			cv.put(KEY_ID, 0);
			long num = mDatabase.insertOrThrow(TABLE_NAME_SEARCH_HISTORY, null, cv);
//			mDatabase.execSQL("insert into " + TABLE_NAME_SEARCH_HISTORY + " (id,word) values(-1,'" + word + "')");
//			String sql = "insert into " + TABLE_NAME_SEARCH_HISTORY + " (id,word) values(-1,'" + word + "')";
			int i = 0;
			i ++;
			mDatabase.setTransactionSuccessful();
		} catch (Exception e) {
			String str = " " + e;
		} finally {
			mDatabase.endTransaction();
		}
		mDatabase.close();
	}
	
	public void insertCallRecord(DMCallRecord record) {
		if (record == null) {
			return;
		}
		mDatabase = mSQLiteOpenHelper.getWritableDatabase();
		try {
			mDatabase.beginTransaction();
			
			ContentValues cv = new ContentValues();
			cv.put(KEY_ID, record.getId());
			cv.put(KEY_TYPE, record.getType());
			if (!UIHelper.isEmpty(record.getSid())) {
				cv.put(KEY_SID, record.getSid());
			}
			cv.put(KEY_VERSION, record.getVersion());
			cv.put(KEY_DEVICE, record.getDevice());
			long num = mDatabase.insertOrThrow(TABLE_NAME_CALL_RECORD, null, cv);
			mDatabase.setTransactionSuccessful();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			mDatabase.endTransaction();
		}
		mDatabase.close();
	}

	public void insertOpenAppRecord(DMOpenApp record) {
		if (record == null) {
			return;
		}
		mDatabase = mSQLiteOpenHelper.getWritableDatabase();
		try {
			mDatabase.beginTransaction();
			
			ContentValues cv = new ContentValues();
			cv.put(KEY_VERSION, record.getVersion());
			cv.put(KEY_DEVICE, record.getDevice());
			long num = mDatabase.insertOrThrow(TABLE_NAME_OPEN_APP_RECORD, null, cv);
			mDatabase.setTransactionSuccessful();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			mDatabase.endTransaction();
		}
		mDatabase.close();
	}
	
	
	
	public ArrayList<DMCallRecord> queryAllCallRecord() {
		ArrayList<DMCallRecord> ret = new ArrayList<DMCallRecord>();
		mDatabase = mSQLiteOpenHelper.getWritableDatabase();
		try {
			mDatabase.beginTransaction();
			Cursor c = mDatabase.rawQuery("SELECT * FROM "
					+ TABLE_NAME_CALL_RECORD, null);
			while (c.moveToNext()) {
				DMCallRecord record = new DMCallRecord();
				record.setDbId(c.getInt(c.getColumnIndex(KEY_DB_ID)));
				record.setDevice(c.getString(c.getColumnIndex(KEY_DEVICE)));
				record.setId(c.getLong(c.getColumnIndex(KEY_ID)));
				record.setSid(c.getString(c.getColumnIndex(KEY_SID)));
				record.setType(c.getInt(c.getColumnIndex(KEY_TYPE)));
				record.setVersion(c.getString(c.getColumnIndex(KEY_VERSION)));
				ret.add(record);
			}
			c.close();
			mDatabase.setTransactionSuccessful();

		} catch (SQLiteException e) {
			createEmptyTables();
		} finally {
			mDatabase.endTransaction();
		}
		mDatabase.close();
		return ret;
	}
	
	public ArrayList<DMOpenApp> queryOpenAppRecord() {
		ArrayList<DMOpenApp> ret = new ArrayList<DMOpenApp>();
		mDatabase = mSQLiteOpenHelper.getWritableDatabase();
		try {
			mDatabase.beginTransaction();
			Cursor c = mDatabase.rawQuery("SELECT * FROM "
					+ TABLE_NAME_OPEN_APP_RECORD, null);
			while (c.moveToNext()) {
				DMOpenApp record = new DMOpenApp();
				record.setDevice(c.getString(c.getColumnIndex(KEY_DEVICE)));
				record.setVersion(c.getString(c.getColumnIndex(KEY_VERSION)));
				record.setDbId(c.getInt(c.getColumnIndex(KEY_DB_ID)));
				ret.add(record);
			}
			c.close();
			mDatabase.setTransactionSuccessful();

		} catch (SQLiteException e) {
			createEmptyTables();
		} finally {
			mDatabase.endTransaction();
		}
		mDatabase.close();
		return ret;
	}
	
	public void deleteCallRecord(DMCallRecord record) {
		if (record == null) {
			return;
		}
		mDatabase = mSQLiteOpenHelper.getWritableDatabase();
		try {
			mDatabase.beginTransaction();
			mDatabase.execSQL("DELETE FROM "
					+ TABLE_NAME_CALL_RECORD + " where _id = " + record.getDbId(), new Object[]{});
			mDatabase.setTransactionSuccessful();
			Utils.logd("record deleted ok =" + record);

		} catch (SQLiteException e) {
			e.printStackTrace();
		} finally {
			mDatabase.endTransaction();
		}
		mDatabase.close();
	}
	
	public void deleteOpenAppRecord(DMOpenApp record) {
		if (record == null) {
			return;
		}
		mDatabase = mSQLiteOpenHelper.getWritableDatabase();
		try {
			mDatabase.beginTransaction();
			mDatabase.execSQL("DELETE FROM "
					+ TABLE_NAME_OPEN_APP_RECORD + " where _id = " + record.getDbId(), new Object[]{});
			mDatabase.setTransactionSuccessful();
			Utils.logd("record deleted ok =" + record);

		} catch (SQLiteException e) {
			e.printStackTrace();
		} finally {
			mDatabase.endTransaction();
		}
		mDatabase.close();
	}
	
	public void emptySearchHistory() {
		mDatabase = mSQLiteOpenHelper.getWritableDatabase();
		try {
			mDatabase.beginTransaction();
			createEmptyTables();
			mDatabase.setTransactionSuccessful();

		} finally {
			mDatabase.endTransaction();
		}
		mDatabase.close();
	}
	
	public ArrayList<String> getSearchHistory() {
		ArrayList<String> ret = new ArrayList<String>();
		mDatabase = mSQLiteOpenHelper.getWritableDatabase();
		try {
			mDatabase.beginTransaction();
			Cursor c = mDatabase.rawQuery("SELECT * FROM "
					+ TABLE_NAME_SEARCH_HISTORY, null);
//			Cursor c = mDatabase.query(TABLE_NAME_SEARCH_HISTORY, null, null, null, null, null, null);
			while (c.moveToNext()) {
				String item = c.getString(c.getColumnIndex(KEY_WORD));
				ret.add(item);
			}
			c.close();
			mDatabase.setTransactionSuccessful();

		} catch (SQLiteException e) {
			createEmptyTables();
		} finally {
			mDatabase.endTransaction();
		}
		mDatabase.close();
		// 返回逆序排序的历史记录
		ArrayList<String> inv = new ArrayList<String>();
		if (ret != null) {
			for (int i = ret.size() - 1; i >= 0; i --) {
				inv.add(ret.get(i));
			}
		}
		return inv;
	}
	
	private void createEmptyTables() {
		// material.db
		mDatabase.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME_SEARCH_HISTORY);
		mDatabase.execSQL("CREATE TABLE " + TABLE_NAME_SEARCH_HISTORY
				+ " (_id INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_ID
				+ " INTEGER," + KEY_WORD + " TEXT)");
	}
	

}
